/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.futco.sysconthos.Modelo.Procesos.Daos;

import org.futco.sysconthos.Modelo.Admisiones.Daos.IDAO;
import org.futco.sysconthos.Modelo.Admisiones.Daos.Model;
import org.futco.sysconthos.Modelo.Proceso.Cita;
import org.futco.sysconthos.Modelo.Proceso.Especialidad;
import org.futco.sysconthos.Modelo.Proceso.Turno;
import com.mysql.jdbc.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

//import org.python.util.JythoncAntTask;

/**
 *
 * @author Server
 */
public class CitaDAO implements IDAO <Cita> {
    private Model model;
    private PreparedStatement psInsert;

    public CitaDAO() throws SQLException {
        model = Model.getInstance();
    }

    public void insert(Cita t) throws SQLException {
       if (psInsert == null) {
            String sqlInsert = "INSERT INTO cita (codigo,fecha,tipoCita,modalidadCita,estadoCita,observaciones,CodMedico,CodPaciente,indiceTurno) VALUES (?,?,?,?,?,?,?,?,?)";
            psInsert = (PreparedStatement) model.getCon().prepareStatement(sqlInsert);
      
        psInsert.setString(1, t.getCodigo());
        psInsert.setString(2, t.getFecha());
        psInsert.setString(3, t.getTipoCita());
        psInsert.setString(4, t.getModalidadCita());
        psInsert.setString(5, t.getEstadoCita());
        psInsert.setString(6, t.getObservaciones());
        psInsert.setString(7, t.getCodMedico());
        psInsert.setString(8, t.getCodPaciente());
        psInsert.setString(9, t.getIndiceTurno());
        psInsert.executeUpdate();
        }
    }

    public void delete(Cita t) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Cita> getAll() throws SQLException {
    	Cita pro=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List lista= new LinkedList();

        try{
            String sqlInsert = "Select * from cita";
            ps=(PreparedStatement) model.getCon().prepareStatement(sqlInsert);
            rs=ps.executeQuery();
            while(rs.next()){
                pro=this.loadCita(rs);
                lista.add(pro);
    }
        }finally{
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
        }
        return lista;
    }

     public Cita find(String codigo) throws SQLException {
        Cita cita = new Cita();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = model.getCon().prepareStatement("SELECT * FROM cita WHERE codigo = ?");
            pst.setString(1,  codigo.trim());
            rs = pst.executeQuery();

            if (rs.next()) {
              cita = loadCita(rs);
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return cita;
    }
   

    public void update(String codigo, Cita t) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Turno> getAllByDispo(String codMed, String fecha) throws SQLException {
         List lista = new LinkedList<Cita>();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        Turno c = null;

        try {           
          if(existeCita(codMed,fecha)!=0){

            pst = model.getCon().prepareStatement("SELECT DISTINCT  t.* FROM cita c, medicohorario mh, turno t WHERE c.codMedico=mh.codMedico AND c.fecha=mh.fecha AND t.codHorario=mh.codHorario and t.indice NOT IN (SELECT t.indice  FROM cita c, medicohorario mh, turno t WHERE c.codMedico=mh.codMedico AND c.fecha=mh.fecha AND t.codHorario=mh.codHorario AND t.indice = c.indiceTurno AND c.codMedico=? AND c.fecha=?) AND c.codMedico=? AND c.fecha=?");
            pst.setString(1,  codMed.trim());
            pst.setString(2,  fecha.trim());
            pst.setString(3,  codMed.trim());
            pst.setString(4,  fecha.trim());
            }else{
            pst = model.getCon().prepareStatement("SELECT t.* FROM  medicohorario mh, turno t WHERE  t.codHorario=mh.codHorario  AND mh.codMedico=? AND mh.fecha=?");
            pst.setString(1,  codMed.trim());
            pst.setString(2,  fecha.trim());
            }
           
            rs = pst.executeQuery();

            while (rs.next()) {
              c=this.loadTurtno(rs);
              lista.add(c);
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return lista;
    }

   private Turno loadTurtno(ResultSet rs) throws SQLException {
       Turno c=new Turno();

       c.setCodHorario(rs.getString(1));
       c.setIndice(rs.getInt(2));
       c.setHoraInicio(rs.getString(3));
       c.setHoraFin(rs.getString(4));

      return c;
    }

   

    public  int NumRegistros()throws SQLException{
        int numfil=0;
            if (model.getCon() == null ) {
          throw new SQLException(" no hay conexion ");
         }
         ResultSet rs = null;
          PreparedStatement pst = null;
           try {
            pst = (PreparedStatement) model.getCon().prepareStatement("select count(*) AS  numfil from cita");
            rs = pst.executeQuery();

             while(rs.next()) {
             numfil = rs.getInt("numfil");
         }
            }finally {
        if (pst != null) {
             pst.close();
        }

   }
         return numfil;
    }

       public  int IncrementarCod()throws SQLException{
        int cod=0;

            if (model.getCon() == null ) {
          throw new SQLException(" no hay conexion ");
         }
         ResultSet rs = null;
          PreparedStatement pst = null;
           try {
            pst = (PreparedStatement) model.getCon().prepareStatement("select max(codigo)+1 AS codInt from cita");
            rs = pst.executeQuery();

             while(rs.next()) {
             cod= rs.getInt("codInt");
         }
            }finally {
        if (pst != null) {
             pst.close();
        }

   }
         return cod;
    }

   /*  public  int existeCita(String codMed, String fecha)throws SQLException{
        int numfil=0;
            if (model.getCon() == null ) {
          throw new SQLException(" no hay conexion ");
         }
         ResultSet rs = null;
          PreparedStatement pst = null;
           try {
            pst = (PreparedStatement) model.getCon().prepareStatement("SELECT count( * )  FROM cita c, medicohorario mh, turno t WHERE c.codMedico=mh.codMedico AND c.fecha=mh.fecha AND t.codHorario=mh.codHorario AND t.indice = c.indiceTurno AND c.codMedico='333' AND c.fecha='2010-12-07'");
            rs = pst.executeQuery();
          //  pst.setString(1,  codMed.trim());
          //  pst.setString(2,  fecha.trim());

             while(rs.next()) {
             numfil = rs.getInt("numfil");
         }
            }finally {
        if (pst != null) {
             pst.close();
        }

   }
        JOptionPane.showMessageDialog(null, numfil);
            return numfil;
    }*/

       public int existeCita(String codMed, String fecha)throws SQLException{
        int numfil=0;
            if (model.getCon() == null ) {
          throw new SQLException(" no hay conexion ");
         }
         ResultSet rs = null;
          PreparedStatement pst = null;
           
          try {
            pst = (PreparedStatement) model.getCon().prepareStatement("SELECT  *  FROM cita c, medicohorario mh, turno t WHERE c.codMedico=mh.codMedico AND c.fecha=mh.fecha AND t.codHorario=mh.codHorario AND t.indice = c.indiceTurno AND c.codMedico=? AND c.fecha=?");
           // JOptionPane.showMessageDialog(null, "LLEGO  "+numfil);
            pst.setString(1,  codMed.trim());
            pst.setString(2,  fecha.trim());

            rs = pst.executeQuery();
              
           
           while(rs.next()) {
           numfil=numfil+1;
           //  numfil = rs.getInt("numfil");
           
         }
            }finally {
        if (pst != null) {
             pst.close();
        }

   }
       
         return numfil;
    }

        public List<Cita> getAllByCita(String codPac) throws SQLException {
        List lista = new LinkedList<Cita>();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        Cita c = null;

        try {
          
            pst = model.getCon().prepareStatement("SELECT c.* from cita c where c.codPaciente=?");
            pst.setString(1,  codPac.trim());
                                  
            rs = pst.executeQuery();

            while (rs.next()) {
              c=this.loadCita(rs);
              lista.add(c);
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return lista;
    }


    public Cita consultarCitaporPaciente(String codPac) throws SQLException {
        Cita cita = new Cita();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = model.getCon().prepareStatement("SELECT * FROM cita WHERE codPaciente = ? GROUP BY codigo");
            pst.setString(1,  codPac.trim());
            rs = pst.executeQuery();

            if (rs.next()) {
              cita = loadCita(rs);
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return cita;
    }
    public List listarCitasDePaciente(String codPac) throws SQLException {
        List lista = new LinkedList();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            pst = model.getCon().prepareStatement("SELECT * FROM cita WHERE codPaciente = ? GROUP BY codigo");
            pst.setString(1,  codPac.trim());
            rs = pst.executeQuery();

            while (rs.next()) {
                lista.add(loadCita(rs));
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return lista;
    }



    private Cita loadCita(ResultSet rs) throws SQLException {

       Cita c=new Cita();
      
       c.setCodigo(rs.getString(1));
       c.setFecha(rs.getString(2));
       c.setTipoCita(rs.getString(3));
       c.setModalidadCita(rs.getString(4));
       c.setEstadoCita(rs.getString(5));
       c.setObservaciones(rs.getString(6));
       c.setCodMedico(rs.getString(7));
       c.setCodPaciente(rs.getString(8));
       c.setIndiceTurno(rs.getString(9));
      return c;
    }

    public boolean  updateEstado(Cita t) throws SQLException {
        Cita cita = new Cita();
        java.sql.PreparedStatement pst = null;
        ResultSet rs = null;
        boolean res = false;
        try {
            pst = model.getCon().prepareStatement("UPDATE cita SET estadoCita = ?  WHERE codigo = ?");
            pst.setString(1,  t.getEstadoCita());
            pst.setString(2,  t.getCodigo());
            int r = pst.executeUpdate();

            if (r!= 0) {
              res = true;
            }

        } finally {
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
        return res;
    }

    public Cita find(Integer id) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public List<Cita> getAllBy(String t, String n) throws SQLException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

	





}

  

